<?php

namespace app\admin\model;

use think\Model;

use think\migration\Migrator;
use think\migration\db\Column;

use think\Db;

class GPSMigration extends Model
{   
    const TABLE_PREFIX = 'gps_gather_detail_%s';

    /**
     * 创建数据库 
     * Author Raven
     * Date 2018-10-07
     * Params [params]
     * @param  string $tab_name [数据表名称]
     * @param  array  $field    [查询的字段]
     */
    public function createTable($tab_name = '', $field = [])
    {
        $tab_name = sprintf(self::TABLE_PREFIX, $tab_name);

        $hasTable = $this->hasTable($tab_name);

        if($hasTable){
            $sql = $this->getModifyTableSql($tab_name, $field);
        }else{
            $sql = $this->getCreateTableSql($tab_name, $field);
        }

        $res = Db::execute($sql);
        return $this->hasTable($tab_name);
    }

    /**
     * 添加数据记录 
     * Author Raven
     * Date 2018-10-11
     * Params [params]
     * @param string $table_name [数据表名称]
     * @param array  $field      [字段列表]
     * @param array  $data       [添加的数据]
     */
    public function addItemInfo($table_name = '', $field = [], $data = [])
    {
        $table_name = sprintf(self::TABLE_PREFIX, $table_name);

        $hasTable = $this->hasTable($table_name);

        if($hasTable == false){
            return false;
        }

        $data = $this->createItemData($data, $field);

        try {
            $res = Db::table($table_name)->insert($data);

            return $res;
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * 判断数据表是否存在 
     * Author Raven
     * Date 2018-10-07
     * Params [params]
     * @param  string  $tab_name [数据表名]
     */
    public function hasTable($tab_name = '')
    {
        $sql = "SHOW CREATE TABLE " . $tab_name;

        try {
            $table = Db::query($sql,[]);

            return true;
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * 获取建表语句 
     * Author Raven
     * Date 2018-10-07
     * Params [params]
     * @param  string  $tab_name [数据表名]
     */
    public function showTable($tab_name = '')
    {
        $sql = "SHOW CREATE TABLE " . $tab_name;

        try {
            $table = Db::query($sql,[]);

            return $table[0]['Create Table'];
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * 生成创建数据表语句 
     * Author Raven
     * Date 2018-10-07
     * Params [params]
     * @param  string $tab_name [数据表名称]
     * @param  array  $field    [添加的字段]
     */
    public function getCreateTableSql($tab_name = '', $field = [])
    {
        $sql = [];
        $sql[] = sprintf("CREATE TABLE `%s` (", $tab_name);
        $sql[] = "\t`id` int(11) NOT NULL AUTO_INCREMENT,";

        foreach ($field as $key => $value) {
            $sql[] = sprintf("\t`%s` varchar(250) NOT NULL DEFAULT '' COMMENT '%s',", $value->item_hex, $value->item_name);
        }

        $sql[] = "\t`data_lng` double(10, 6) NOT NULL DEFAULT '-180' COMMENT '采集数据经度',";
        $sql[] = "\t`data_lat` double(10, 6) NOT NULL DEFAULT '-90' COMMENT '采集数据维度',";
        $sql[] = "\t`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-删除 3-异常',";
        $sql[] = "\t`create_time` int(11) NOT NULL DEFAULT '0' COMMENT '数据创建时间',";
        $sql[] = "\t`update_time` int(11) NOT NULL DEFAULT '0' COMMENT '数据最后更新时间',";
        $sql[] = "\tPRIMARY KEY (`id`)";
        $sql[] = ') ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4';

        $res = implode("\n", $sql);

        return $res;
    }

    /**
     * 生成修改表语句 
     * Author Raven
     * Date 2018-10-07
     * Params [params]
     * @param  string $tab_name [数据表名称]
     * @param  array  $field    [添加的字段]
     */
    public function getModifyTableSql($tab_name = '', $field = [])
    {
        $column = $this->getColumnNameByTable($tab_name);

        $sql[] = sprintf("ALTER TABLE `%s` ", $tab_name);

        foreach ($field as $key => $value) {
            if(isset($column[$value->item_hex]) == false){
                $sql[] = sprintf("ADD COLUMN `%s` VARCHAR(250) NOT NULL DEFAULT '' COMMENT '%s',", $value->item_hex, $value->item_name);
            }
        }

        $res = implode("\n ", $sql);

        $res = substr($res, 0, strlen($res) - 1) . ";";

        return $res;
    }

    /**
     * 查询表下的字段列表 
     * Author Raven
     * Date 2018-10-07
     * Params [params]
     * @param  string $tab_name [数据表名称]
     */
    public function getColumnNameByTable($tab_name = '')
    {
        $sql = sprintf("select COLUMN_NAME from information_schema.columns where table_name='%s'", $tab_name);
        $column = Db::query($sql);

        $res = [];

        foreach($column as $value){
            $res[$value['COLUMN_NAME']] = 1;
        }

        return $res;
    }

    /**
     * 生成表单保存对象 
     * Author Raven
     * Date 2018-10-11
     * Params [params]
     * @param  array  $data  [数据对象]
     * @param  array  $field [查询的字段]
     */
    public function createItemData($data = [], $field = [])
    {
        $res = [];

        foreach ($field as $key => $value) {
            $item_key = $value->item_hex;
            $item_type = $value->item_type;

            if(isset($data['item_' . $item_key])){
                //item_type == 3 为多选类型
                $res[$item_key] = $item_type == 3 ? implode("|", $data['item_' . $item_key]) : $data['item_' . $item_key];
            }
        }

        $res['data_lng'] = $data['data_lng'];
        $res['data_lat'] = $data['data_lat'];
        $res['create_time'] = time();
        $res['update_time'] = time();
        return $res;
    }

    /**
     * 获取表格数据 
     * Author Raven
     * Date 2018-10-18
     * Params [params]
     * @param  string  $table_name [数据表名称]
     * @param  array   $field      [查询的字段]
     * @param  integer $page_size  [每页记录条数]
     */
    public function getTableData($table_name = '', $field = [], $page_size = 20)
    {
        $data = Db::table($table_name)
            ->field($field)
            ->order("id desc")
            ->paginate($page_size);

        // 获取分页显示
        $page = $data->render();

        $res = [
            'list' => $data->toArray(),
            'page' => $page
        ];

        return $res;
    }

    /**
     * 获取详情信息 
     * Author Raven
     * Date 2018-10-18
     * Params [params]
     * @param  string  $table_name [数据表名称]
     * @param  array   $field      [查询的字段]
     * @param  integer $detail_id  [数据id]
     */
    public function getTableDetail($table_name = '', $field = [], $detail_id = 0)
    {
        $res = Db::table($table_name)
            ->where('id', $detail_id)
            ->field($field)
            ->find();

        return $res;
    }

    /**
     * 更新详情信息 
     * Author Raven
     * Date 2018-10-20
     * Params [params]
     * @param  string  $table_name [数据表名称]
     * @param  integer $detail_id  [数据id]
     * @param  array   $data       [保存的数据对象]
     */
    public function updateTableDetail($table_name = '', $detail_id = 0, $data = [])
    {
        $res = Db::table($table_name)
            ->where('id', $detail_id)
            ->update($data);

        return $res;
    }
}
